package moveMysql;

public class Constant {

    public static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    //表名
    public static String[] tableNames = {"subsite", "subject", "case_entity", "case_extends", "target", "target_extends", "case_permission", "target_permission"};
    //查询sql语句
    //*********************************************test,不查询visit_control表是否有效
    /*
    public static String getCaseEntityDataSql = "select case_entity.* from case_entity";
    //查询case_extends表
    public static String getCaseExtendsDataSql = "select ce.* from " +
            "case_extends ce,case_entity c where  ce.id = c.id ";
    //查询target表
    public static String getTargetDataSql = "select target.* from target ";
    //查询target_extends表
    public static String getTargetExtendsDataSql = "select te.* from " +
            "target t,target_extends te where te.target_id = t.id ";
    //查询subject表
    public static String getSubjectDataSql = "select * from subject";
    //查询subsite表
    public static String getSubsiteDataSql = "select * from subsite";

    //按case的id查询case_entity表
    public static String getCaseEntityDataSqlById = "select c.* from " +
            "case_entity c where  c.id = ? ";
    //按case的id查询case_extends表
    public static String getCaseExtendsDataSqlById = "select ce.* from " +
            "case_extends ce,case_entity c where c.id = ? AND ce.id = c.id ";
    //*********************************************test
    */
    //查询visit_control是否有效
    //查询case_entity表
    public static String getCaseEntityDataSql = "select c.* from " +
            "case_entity c,visit_control v where " +
            "(v.entity_type = 'case' " +
            "AND c.id = v.entity_id " +
            "AND v.valid = 1)";
    //查询case_extends表
    public static String getCaseExtendsDataSql = "select ce.* from " +
            "case_extends ce,case_entity c,visit_control v where " +
            "(v.entity_type = 'case' " +
            "AND ce.id = c.id " +
            "AND c.id = v.entity_id " +
            "AND v.valid = 1)";
    //查询target表
    public static String getTargetDataSql = "select t.* from " +
            "target t,visit_control v where " +
            "(v.entity_type = 'target' " +
            "AND t.id = v.entity_id " +
            "AND v.valid = 1)";
    //查询target_extends表
    public static String getTargetExtendsDataSql = "select te.* from " +
            "target t,visit_control v ,target_extends te where " +
            "(v.entity_type = 'target' " +
            "AND t.id = v.entity_id " +
            "AND te.target_id = t.id " +
            "AND v.valid = 1)";
    //查询subject表
    public static String getSubjectDataSql = "select * from subject";
    //查询subsite表
    public static String getSubsiteDataSql = "select * from subsite";

    //按case的id查询case_entity表
    public static String getCaseEntityDataSqlById = "select c.* from " +
            "case_entity c,visit_control v where " +
            "( v.entity_type = 'case' " +
            "AND c.id = ? " +
            "AND c.id = v.entity_id " +
            "AND v.valid = 1 )";
    //按case的id查询case_extends表
    public static String getCaseExtendsDataSqlById = "select ce.* from " +
            "case_extends ce,case_entity c,visit_control v where " +
            "(v.entity_type = 'case' " +
            "AND c.id = ? " +
            "AND ce.id = c.id " +
            "AND c.id = v.entity_id " +
            "AND v.valid = 1)";
    //************************************************end
    //创建case_entity表的sql语句
    public static String SQL_CREATE_CASE_ENTITY_TABLE = "CREATE TABLE `case_entity` (" +
            "`id` int(32) NOT NULL AUTO_INCREMENT," +
            "`name` varchar(50) NOT NULL," +
            "`difficulty` int(11) unsigned zerofill NOT NULL DEFAULT '00000000001'," +
            "`domain` int(8) NOT NULL DEFAULT '0'," +
            "`description` varchar(2000) DEFAULT NULL," +
            "`subject_id` int(32) NOT NULL," +
            "`case_id` varchar(50) NOT NULL," +
            "`is_public` int(1) NOT NULL DEFAULT '1'," +
            "`create_time` datetime DEFAULT NULL," +
            "`app_id` bigint(20) DEFAULT NULL," +
            "`is_deleted` tinyint(4) NOT NULL DEFAULT '0'," +
            "`owner_id` bigint(50) DEFAULT NULL," +
            "`platform` smallint(6) DEFAULT NULL," +
            "`properties` mediumtext," +
            "`subsite_id` bigint(20) DEFAULT NULL," +
            "PRIMARY KEY (`id`)," +
            "UNIQUE KEY `id` (`id`) USING BTREE," +
            "KEY `subject_id` (`subject_id`) USING BTREE," +
            "KEY `subject&deleted_idx` (`subject_id`,`is_deleted`)," +
            "KEY `public&deleted_idx` (`is_public`,`is_deleted`)," +
            "KEY `manager&public&deleted_idx` (`is_public`,`is_deleted`)," +
            "CONSTRAINT `case_entity_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`)" +
            ") ENGINE=InnoDB AUTO_INCREMENT=658 DEFAULT CHARSET=utf8;";
    //创建case_extends表的sql语句
    public static String SQL_CREATE_CASE_EXTENDS_TABLE = "CREATE TABLE `case_extends` (" +
            "  `platform` smallint(6) DEFAULT NULL," +
            "  `properties` mediumtext," +
            "  `subsite_id` bigint(20) DEFAULT NULL," +
            "  `target_id` bigint(20) DEFAULT NULL," +
            "  `id` bigint(20) NOT NULL," +
            "  PRIMARY KEY (`id`)," +
            "  KEY `platform_idx` (`platform`)," +
            "  KEY `subsite_id_idx` (`subsite_id`)," +
            "  KEY `target_id_idx` (`target_id`)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    //创建target表的sql语句
    public static String SQL_CREATE_Target_TABLE = "CREATE TABLE `target` (" +
            "  `id` bigint(20) NOT NULL AUTO_INCREMENT," +
            "  `is_public` bit(1) DEFAULT NULL," +
            "  `name` varchar(255) NOT NULL DEFAULT ''," +
            "  `url` varchar(255) DEFAULT NULL," +
            "  `subsite_id` bigint(20) NOT NULL," +
            "  `status` int(1) NOT NULL DEFAULT '0'," +
            "  `create_time` datetime DEFAULT NULL," +
            "  `message` varchar(255) DEFAULT NULL," +
            "  `owner_id` bigint(50) DEFAULT NULL," +
            "  PRIMARY KEY (`id`)," +
            "  KEY `owner_id_idx` (`owner_id`)" +
            ") ENGINE=InnoDB AUTO_INCREMENT=654 DEFAULT CHARSET=utf8;";
    //创建target_extends表的sql数据
    public static String SQL_CREATE_Target_Extends_TABLE = "CREATE TABLE `target_extends` (" +
            "  `id` bigint(20) NOT NULL AUTO_INCREMENT," +
            "  `target_id` bigint(20) NOT NULL," +
            "  `property_name` char(255) NOT NULL," +
            "  `property_value` char(255) NOT NULL," +
            "  PRIMARY KEY (`id`)" +
            ") ENGINE=InnoDB AUTO_INCREMENT=1011 DEFAULT CHARSET=utf8;";

    //创建case_permission表的sql语句
    public static String SQL_CREATE_CASE_PERMISSION_TABLE = "CREATE TABLE `case_permission` (" +
            "  `id` bigint(50) unsigned NOT NULL AUTO_INCREMENT," +
            "  `user_id` bigint(50) DEFAULT NULL," +
            "  `operation` varchar(50) DEFAULT NULL," +
            "  `instance_id` bigint(50) DEFAULT NULL," +
            "  `create_time` datetime DEFAULT NULL," +
            "  PRIMARY KEY (`id`)," +
            "  KEY `user_id_idx` (`user_id`)," +
            "  KEY `instance_id_idx` (`instance_id`)" +
            ") ENGINE=InnoDB AUTO_INCREMENT=854 DEFAULT CHARSET=latin1;";
    //创建target_permission表的sql语句
    public static String SQL_CREATE_TARGET_PERMISSION_TABLE = "CREATE TABLE `target_permission` (" +
            "  `id` bigint(50) unsigned NOT NULL AUTO_INCREMENT," +
            "  `user_id` bigint(50) DEFAULT NULL," +
            "  `operation` varchar(50) DEFAULT NULL," +
            "  `instance_id` bigint(50) DEFAULT NULL," +
            "  `create_time` datetime DEFAULT NULL," +
            "  PRIMARY KEY (`id`)" +
            ") ENGINE=InnoDB AUTO_INCREMENT=355 DEFAULT CHARSET=latin1;";
    //创建subject表的sql语句
    public static String SQL_CREATE_SUBJECT_TABLE = "CREATE TABLE `subject` (" +
            "  `id` int(32) NOT NULL AUTO_INCREMENT," +
            "  `subsite_id` int(32) NOT NULL," +
            "  `name` varchar(50) NOT NULL," +
            "  `target_url` varchar(80) NOT NULL," +
            "  `type_name` varchar(100) DEFAULT NULL," +
            "  PRIMARY KEY (`id`)," +
            "  UNIQUE KEY `id` (`id`)," +
            "  KEY `subject_ibfk_1` (`subsite_id`)," +
            "  KEY `subsite&typename_idx` (`subsite_id`,`type_name`)," +
            "  KEY `subsite_idx` (`subsite_id`)," +
            "  CONSTRAINT `subject_ibfk_1` FOREIGN KEY (`subsite_id`) REFERENCES `subsite` (`id`)" +
            ") ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;";
    //创建subsite表的sql语句
    public static String SQL_CREATE_SUBSITE_TABLE = "CREATE TABLE `subsite` (" +
            "  `id` int(32) NOT NULL AUTO_INCREMENT," +
            "  `name` varchar(50) NOT NULL," +
            "  `base_url` varchar(200) NOT NULL," +
            "  `type` varchar(255) DEFAULT NULL," +
            "  `worker_entrance_url` varchar(255) DEFAULT NULL," +
            "  `manager_entrance_url` varchar(255) DEFAULT NULL," +
            "  PRIMARY KEY (`id`)," +
            "  UNIQUE KEY `id` (`id`) USING BTREE," +
            "  KEY `baseurl_idx` (`base_url`)" +
            ") ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;";


}
